package com.telecom.cms.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class CrmCmsCustInfo {
	public static void main(String[] args) throws Exception{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		Connection connCRM = DriverManager.getConnection("jdbc:oracle:thin:@134.175.7.87:1521:test02","CMSUSER","cms123"); 
		Statement stmtCRM = connCRM.createStatement();
		stmtCRM.executeUpdate("delete from TBM_CAMP_CUST_INFO_FOR_CRM");
		
		DriverManager.registerDriver(new com.ncr.teradata.TeraDriver());
		Connection connCMS = DriverManager.getConnection("jdbc:teradata://134.175.7.7/CLIENT_CHARSET=CP936,TMODE=TERA,CHARSET=ASCII,database=PD_MARTMETA_CAMP,LOB_Support=OFF","pu_qck","pu_qck");
		Statement stmtCMS = connCMS.createStatement();
		
		StringBuffer sb = new StringBuffer();
		ResultSet rs = stmtCMS.executeQuery("SELECT Cust_Id,Prd_Inst_Id,Prd_Cd,Prd_Name,Ofr_Cd,Ofr_Name,Camp_Id,Camp_Name,Camp_Adv,"
			+ "'20' || Eff_Date Eff_Date,'20' || Exp_Date Exp_Date,Latn_Id "
			+ "FROM PD_MARTMETA_CAMP.CAMP_CUST_INFO_FOR_CRM WHERE Camp_Id NOT IN(2800002,3300001)"
			+ "QUALIFY ROW_NUMBER() OVER(PARTITION BY Cust_Id,Prd_Inst_Id,Camp_Id ORDER BY Camp_Adv)=1");
		while (rs.next()){
			String sql = new String("INSERT INTO TBM_CAMP_CUST_INFO_FOR_CRM(" +
					"Cust_id" +
					",Prd_Inst_id" +
					",Prd_Cd" +
					",Prd_Name" +
					",Ofr_Cd" +
					",Camp_Id" +
					",Camp_Name" +
					",Camp_Adv" +
					",Eff_date" +
					",Exp_date" +
					",areaCode)" +
				"VALUES(" +
					(rs.getString("Cust_Id").trim().equals("") ? "0" : rs.getString("Cust_Id")) + "," +
					(rs.getString("Prd_Inst_Id").trim().equals("") ? "0" : rs.getString("Prd_Inst_Id")) + ",'" +
					rs.getString("Prd_Cd") + "','" +
					rs.getString("Prd_Name") + "'," +
					(rs.getString("Ofr_Cd").trim().equals("") ? "null" : rs.getString("Ofr_Cd")) + "," +
					rs.getString("Camp_Id") + ",'" +
					rs.getString("Camp_Name") + "','" +
					rs.getString("Camp_Adv") + "'," +
					"to_date('" + rs.getString("Eff_Date") + "','YYYY-MM-DD')," +
					"to_date('" + rs.getString("Exp_Date") + "','YYYY-MM-DD'),'" +
					rs.getString("Latn_Id") + "')");
			System.out.println(sql);
			stmtCRM.executeUpdate(sql);
		}
		
	//	ResultSet rs = stmtCRM.executeQuery("select * from TBM_CAMP_CUST_INFO_FOR_CRM");
	//	ResultSetMetaData md = rs.getMetaData();
	//	for (int i = 1; i <= md.getColumnCount(); i++){
	//		System.out.println(md.getColumnName(i) + " : " + md.getColumnLabel(i) + " : " + md.getColumnType(i) + " : " + md.getColumnTypeName(i) + " : " + md.getColumnDisplaySize(i) + " : " + md.getColumnClassName(i));
	//	}
	}
}